<?php
/**
 * Created by PhpStorm.
 * User: Vova
 * Date: 9/26/14
 * Time: 9:48 PM
 */

require_once("C:/xampp/htdocs/door-market/php/classes/Host_config.class.php");

class db {
    private $host     = "localhost";
    private $database = "door_market";
    private $username = "root";
    private $password = "";
    private $con;

    protected static $_instance;

    /**
     * Закрываем доступ к функции вне класса.
     * Паттерн Singleton не допускает вызов
     * этой функции вне класса
     *
     */
    private function __construct(){
        $this->con = mysqli_connect($this->host, $this->username, $this->password, $this->database) or die("Some error occurred during connection " . mysqli_error($this->con));
    }

    /**
     * Закрываем доступ к функции вне класса.
     * Паттерн Singleton не допускает вызов
     * этой функции вне класса
     *
     */
    private function __clone(){
    }
    /**
     * Статическая функция, которая возвращает
     * экземпляр класса или создает новый при
     * необходимости
     *
     * @return db
     */
    public static function getInstance() {
        // проверяем актуальность экземпляра
        if (null === self::$_instance) {
            // создаем новый экземпляр
            self::$_instance = new self();
        }
        // возвращаем созданный или существующий экземпляр
        return self::$_instance;
    }
    public function test(){

        echo 'Hi!!';
    }

    public function item_exists($item_no){
        $result = false;
        $sql="select
                          i.item_id
                          from item i
                          WHERE i.item_id = ".$item_no."";


        if ($tmp=mysqli_query($this->con,$sql))
        {
            // Return the number of rows in result set
            $rowcount=mysqli_num_rows($tmp);
            if($rowcount>0){$result = true;}
            mysqli_free_result($tmp);
        }
        return $result;
    }
    public function get_item_info($item_no,$rel_host_name){

       /* $item_info = array(
            "item_id" => '',
            "item_name" => '',
            "item_code" => '',
            "main_mid_photo_url" =>'',
            "main_lar_photo_url" =>'',
            "photo_list" => array("sm_url" => '',
                "lar_url" => ''),
            "feature_list" => array(
                "feature_name" =>'',
                "feature_code" =>''
            )
        );*/




        $feature_list = array( array(
            "feature_name" =>'Матеріал:',
            "feature_value" =>'натуральна сосна'
        ),
            array(
            "feature_name" =>'Матеріал:',
            "feature_value" =>'натуральна сосна'
        ),
            array(
            "feature_name" =>'Колір:',
            "feature_value" =>'доступна велика палітра якісних німецьких фарб та лаків на вибір'
        ),
            array(
            "feature_name" =>'Переглянути список фарб:',
            "feature_value" =>'фарби та лаки'
        ),
            array(
            "feature_name" =>'В комплект входять:',
            "feature_value" =>'сівсі мвомшова мщукомшавом мшкмшватм мшуктмшуктм мтукшатукгшщм штагшукташ атукгаиіво'
        ),
            array(
                "feature_name" =>'Загальна характеристика:',
                "feature_value" =>'сівсі мвомшова мщукомшавом мшкмшватм мшуктмшуктм мтукшатукгшщм штагшукташ атукгаиіво'
            ));



        $sql_main  = "select
                          i.item_id,
                          i.item_name,
                          i.item_code,
                          i.item_description,
                          p.photo_name mid_photo,
                          p.folder mid_folder,
                          p1.photo_name lar_photo,
                          p1.folder lar_folder
                          from item i JOIN photo p on i.item_id = p.item_id
                          JOIN photo p1 ON p1.id_photo = p.large_photo_id
                          WHERE i.item_id = ".$item_no." AND p.photo_type = 'midle_image'  /*catalog_image*/
                          LIMIT 0,1";

        if ($result = mysqli_query($this->con,$sql_main)) {
            while ($row = $result->fetch_array()){

                    $item_id = $row['item_id'];
                    $item_name = $row['item_name'];
                    $item_code = $row['item_code'];
                    $main_mid_photo_url =$rel_host_name."/".$row['mid_folder'] ."/".$row['mid_photo'];
                    $main_lar_photo_url =$rel_host_name."/".$row['lar_folder'] ."/".$row['lar_photo'];

            }
            /* очищаем результирующий набор */
            $result->close();
        }



        $photos_sql = "select
                          p.photo_name sm_photo,
                          p.folder sm_folder,
                          p1.photo_name lar_photo,
                          p1.folder lar_folder
                          from item i JOIN photo p on i.item_id = p.item_id
                          JOIN photo p1 ON p1.id_photo = p.large_photo_id
                          WHERE i.item_id = ".$item_no." AND p.photo_type = 'small_image'";

        $photo_list =  array();

        if ($result = mysqli_query($this->con,$photos_sql)) {
            while ($row = $result->fetch_array()){

                $sm_photo_url =$rel_host_name."/".$row['sm_folder'] ."/".$row['sm_photo'];
                $lar_photo_url =$rel_host_name."/".$row['lar_folder'] ."/".$row['lar_photo'];

                $photo_item = array('sm_url' => $sm_photo_url,'lar_url' =>$lar_photo_url);
                array_push($photo_list,$photo_item);

            }
            /* очищаем результирующий набор */
            $result->close();
        }



        $item_info = array(
            "item_id" => $item_id,
            "item_name" => $item_name,
            "item_code" => $item_code,
            "main_mid_photo_url" =>$main_mid_photo_url,
            "main_lar_photo_url" =>$main_lar_photo_url,
            "photo_list" => $photo_list,
            "feature_list" => $feature_list
        );

        return $item_info;

    }

    public function get_catalog_items_for_page($category,$page_number,$page_size){

        $rel_path = Host_config::$host_name."python_scripts/";

 
        #echo "category = ".strtoupper($category);
        $sql = "SELECT   i.item_id,  i.item_name as item_name,
                 i.item_code,
                 CONCAT(p_catalog.folder,'/',p_catalog.photo_name) AS catalog_photo,
                 CONCAT(p_larg.folder,'/',p_larg.photo_name) AS large_photo
                 FROM item i
                 JOIN photo p_catalog ON p_catalog.item_id=i.item_id AND p_catalog.photo_type = 'catalog_image'
                 join  photo p_larg ON p_larg.item_id=i.item_id AND p_catalog.large_photo_id = p_larg.id_photo 
                 WHERE upper(i.category) ='".strtoupper($category)."' limit ".($page_number-1)*$page_size.",".($page_number)*$page_size."";
        #echo "SQL = ".$sql;
        $items_line = array();
        $result_items = array();
        if ($result = mysqli_query($this->con,$sql)) {
            //echo "yes<br>";
            $i = 0;
            $length = mysqli_num_rows($result);
            while ($row = $result->fetch_array()) {
                //echo "here<br>".$length;
                $item = array(
                    "product_name" => $row['item_name'],
                    "product_code" => $row['item_code'],
                    "item_no" => $row['item_id'],
                    "link_to_detail" => "#",
                    "large_image" => $rel_path.$row['large_photo'],
                    "mid_image" => $rel_path.$row['catalog_photo']
                );
                array_push($items_line ,$item);
                if(($i+1)%3 == 0 || ($i+1)==$length){

                    array_push($result_items,$items_line);
                    $items_line = array();
                }
                $i++;
            }
            /* очищаем результирующий набор */
            $result->close();
        }
        return $result_items;
    }
    public function get_total_pages_number($category,$sub_category,$page_number,$page_size){
    $sql = "SELECT   count(i.item_id) as c
                 FROM item i
                 JOIN photo p_catalog ON p_catalog.item_id=i.item_id AND p_catalog.photo_type = 'catalog_image'
                 join  photo p_larg ON p_larg.item_id=i.item_id AND p_catalog.large_photo_id = p_larg.id_photo
                 WHERE i.category ='".strtoupper($category)."' ";

    $tmp_resulet_val = 0;

    if ($result = mysqli_query($this->con, $sql)) {

        /* fetch associative array */
        while ($row = mysqli_fetch_row($result)) {
            $tmp_resulet_val = $row[0];
        }

        /* free result set */
        mysqli_free_result($result);
    }

        $tmp_resulet_val = floor($tmp_resulet_val/$page_size) + ceil(($tmp_resulet_val%$page_size)/$page_size);

    return $tmp_resulet_val;

}
    public function get_total_items_count($category,$sub_category,$page_number,$page_size){
        $sql = "SELECT   count(i.item_id)
                 FROM item i
                 JOIN photo p_catalog ON p_catalog.item_id=i.item_id AND p_catalog.photo_type = 'catalog_image'
                 join  photo p_larg ON p_larg.item_id=i.item_id AND p_catalog.large_photo_id = p_larg.id_photo
                 WHERE i.category ='".strtoupper($category)."' ";

        $tmp_resulet_val = 0;

        if ($result = mysqli_query($this->con, $sql)) {

            /* fetch associative array */
            while ($row = mysqli_fetch_row($result)) {
                $tmp_resulet_val = $row[0];
            }

            /* free result set */
            mysqli_free_result($result);
        }
        return $tmp_resulet_val;

    }
    public function get_items_count($category,$sub_category){
        $sql = "SELECT   count(i.item_id)
                 FROM item i
                 JOIN photo p_catalog ON p_catalog.item_id=i.item_id AND p_catalog.photo_type = 'catalog_image'
                 join  photo p_larg ON p_larg.item_id=i.item_id AND p_catalog.large_photo_id = p_larg.id_photo
                 WHERE i.category ='".strtoupper($category)."' ";

        $tmp_resulet_val = 0;

        if ($result = mysqli_query($this->con, $sql)) {

            /* fetch associative array */
            while ($row = mysqli_fetch_row($result)) {
                $tmp_resulet_val = $row[0];
            }

            /* free result set */
            mysqli_free_result($result);
        }
        return $tmp_resulet_val;

    }
    public function generate_items_code(){

        $sql = "SELECT * FROM item i where ITEM_CODE is null";

        if ($result = mysqli_query($this->con,$sql)) {
            $i = 0;
            $length = mysqli_num_rows($result);
            while ($row = $result->fetch_array()) {

                $item_id =$row['item_id'];

                echo "Iteration $i <br>";
                echo "-    Item id =  $item_id <br>";
                $code = $this->get_random_code();
                echo "    generated_code = $code<br>";

                /*$exist_flag = $this->check_code($code);

                echo "    exist_flag = $exist_flag<br>";*/

                $this->update_item_code($item_id,$code);
                /*if ($exist_flag == 0){
                    echo "     Value with code $exist_flag doesnt not exist";
                    $this->update_item_code($item_id,$code);
                }
                if ($exist_flag == 1){
                    echo "     Value with code $exist_flag exist";
                    $ii = 0;
                    echo "Search new val";
                    do{
                        $code = $this->get_random_code();
                        echo "    NEW generated_code = $code<br>";

                        $exist_flag = $this->check_code($code);

                        echo "    NEW exist_flag = $exist_flag<br>";

                    }while ($exist_flag==1);
                    $this->update_item_code($item_id,$code);
                }*/


                /*if(($i+1)%3 == 0 || ($i+1)==$length){

                    array_push($result_items,$items_line);
                    $items_line = array();
                }*/
                $i++;
                echo "<br><br>";
            }
            /* очищаем результирующий набор */
            $result->close();
        }

    }
    public function get_random_code(){
        return mt_rand(10000,99999) ;
    }
    public function check_code($code){
        $sql = "SELECT * FROM item i where i.item_code = '" .$code."'";

        $tmp_resulet_val = 0;

        if ($result = mysqli_query($this->con, $sql)) {

            /* fetch associative array */
            while ($row = mysqli_fetch_row($result)) {
                $tmp_resulet_val = 1;
            }
            /* free result set */
            mysqli_free_result($result);
        }
        return $tmp_resulet_val;
    }
    public function update_item_code($item_id,$item_code){
        $sql = "UPDATE ITEM SET ITEM_CODE = '" .$item_code."' WHERE ITEM_ID = " .$item_id ."";
        echo "sql = $sql";

        $result = mysqli_query($this->con, $sql);
        echo "error = " .mysqli_error($this->con);
        echo " update result = $result";
    }

} 